/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package aptech.semester.code.data;

import aptech.semester.code.common.ApplicationCommon;
import aptech.semester.code.conn.MyLib;
import aptech.semester.code.entity.EmployeeEntity;
import java.io.FileInputStream;
import java.sql.*;
import java.util.*;
import javax.swing.JOptionPane;
public class EmployeeList {
        public List<EmployeeEntity> getList() {
        //tra ve 1 danh sach cac doi tuong nguoi dung trong bang Tbuser cua database
      List<EmployeeEntity> ds = new ArrayList<>();
        try {
            //1.tao 1 ket noi
            Connection cn = MyLib.getCN();
            if(cn!=null) {
            //2. tao doi tuong statement chua lenh select sql
            Statement st = cn.createStatement();
            
            //3.cho thi hanh lenh truy van tren doi tuong nay va ket qua se dc tra ve doi tuong resulltset
            ResultSet rs = st.executeQuery("SELECT EMPLOYEE.*, DEPARTMENT.DEPT_NAME, BANK.BANK_NAME, "+
                        "EDUCATION_LEVEL.EDUCATION_LEVEL_NAME, PROVINCE.PROVINCE_NAME, POSITION.POSITION_NAME, SPECIALIZE.SPECIALIZE_NAME " +
                        "FROM EMPLOYEE,DEPARTMENT,BANK,EDUCATION_LEVEL,PROVINCE,POSITION,SPECIALIZE " +
                        "WHERE DEPARTMENT.ID = EMPLOYEE.DEPARTMENT_ID AND BANK.ID = EMPLOYEE.BANK_ID AND " +
                        "EDUCATION_LEVEL.ID = EMPLOYEE.EDUCATION_LEVEL_ID AND EMPLOYEE.PROVINCE_ID = PROVINCE.ID " +
                        "AND EMPLOYEE.POSITION_ID = POSITION.ID AND EMPLOYEE.SPECIALIZED_ID = SPECIALIZE.ID");
            
            //4. duyuet ket qua tra ve trong resultset rs va fill vao arraylist ds.
            while(rs.next()) {
                EmployeeEntity emp = new EmployeeEntity();
                emp.setEmployeeId(rs.getString("ID"));
                emp.setName(rs.getString("NAME"));
                emp.setBirthday(rs.getDate("BIRTHDAY"));
                emp.setGender(rs.getBoolean("GENDER"));
                emp.setEmail(rs.getString("EMAIL"));
                emp.setAddress(rs.getString("ADDRESS"));
                emp.setPhone(rs.getString("PHONE"));
                emp.setAvatar(ApplicationCommon.getAvatarFormDB(rs.getBytes("AVATAR")));
                emp.setEntryDate(rs.getDate("ENTRY_DATE"));
                emp.setBasicSalary(rs.getBigDecimal("BASIC_SALARY"));
                emp.setProvinceName(rs.getString("PROVINCE_NAME"));
                emp.setBankName(rs.getString("BANK_NAME"));
                emp.setAccountNumberBank(rs.getString("ACCOUNT_BANK"));
                emp.setDepartmentName(rs.getString("DEPT_NAME"));
                emp.setDepartmentId(rs.getInt("DEPARTMENT_ID"));
                emp.setPositionName(rs.getString("POSITION_NAME"));
                emp.setMaritalStatus(rs.getBoolean("MARITAL_STATUS"));
                emp.setIsActive(rs.getBoolean("ACTIVE"));
                emp.setEducationLevel(rs.getString("EDUCATION_LEVEL_NAME"));
                emp.setIdentifyNumber(rs.getInt("IDENTIFY_NUMBER"));
                emp.setSpecialize(rs.getString("SPECIALIZE_NAME"));
                
                ds.add(emp);
            }
            rs.close();
            cn.close();
            }
        } catch (Exception e) {
            
        }
        
        return ds;
    }
    
    public List<EmployeeEntity> getEmployeeByDepartment(int departmentId) {
        List<EmployeeEntity> listEmp = new ArrayList<>();
        
        try {
            //1.tao 1 ket noi
            Connection cn = MyLib.getCN();
            if(cn!=null) {
            String sql = "SELECT EMPLOYEE.*, DEPARTMENT.DEPT_NAME, BANK.BANK_NAME, "+
                        "EDUCATION_LEVEL.EDUCATION_LEVEL_NAME, PROVINCE.PROVINCE_NAME, POSITION.*, SPECIALIZE.SPECIALIZE_NAME " +
                        "FROM EMPLOYEE,DEPARTMENT,BANK,EDUCATION_LEVEL,PROVINCE,POSITION,SPECIALIZE " +
                        "WHERE DEPARTMENT.ID = EMPLOYEE.DEPARTMENT_ID AND BANK.ID = EMPLOYEE.BANK_ID AND " +
                        "EDUCATION_LEVEL.ID = EMPLOYEE.EDUCATION_LEVEL_ID AND EMPLOYEE.PROVINCE_ID = PROVINCE.ID " +
                        "AND EMPLOYEE.POSITION_ID = POSITION.ID AND EMPLOYEE.SPECIALIZED_ID = SPECIALIZE.ID " +
                        "AND EMPLOYEE.DEPARTMENT_ID = ? "+
                        "AND EMPLOYEE.ACTIVE = ?";
            PreparedStatement pst = cn.prepareStatement(sql);
            pst.setInt(1, departmentId);
            pst.setBoolean(2, true);
            //3.cho thi hanh lenh truy van tren doi tuong nay va ket qua se dc tra ve doi tuong resulltset
            ResultSet rs = pst.executeQuery();
            
            //4. duyuet ket qua tra ve trong resultset rs va fill vao arraylist ds.
            while(rs.next()) {
                EmployeeEntity emp = new EmployeeEntity();
                emp.setEmployeeId(rs.getString("ID"));
                emp.setName(rs.getString("NAME"));
                emp.setBirthday(rs.getDate("BIRTHDAY"));
                emp.setGender(rs.getBoolean("GENDER"));
                emp.setEmail(rs.getString("EMAIL"));
                emp.setAddress(rs.getString("ADDRESS"));
                emp.setPhone(rs.getString("PHONE"));
                emp.setAvatar(ApplicationCommon.getAvatarFormDB(rs.getBytes("AVATAR")));
                emp.setEntryDate(rs.getDate("ENTRY_DATE"));
                emp.setBasicSalary(rs.getBigDecimal("BASIC_SALARY"));
                emp.setProvinceName(rs.getString("PROVINCE_NAME"));
                emp.setBankName(rs.getString("BANK_NAME"));
                emp.setAccountNumberBank(rs.getString("ACCOUNT_BANK"));
                emp.setDepartmentName(rs.getString("DEPT_NAME"));
                emp.setPositionName(rs.getString("POSITION_NAME"));
                emp.setBonusSalary(rs.getBigDecimal("BONUS_SALARY"));
                emp.setMaritalStatus(rs.getBoolean("MARITAL_STATUS"));
                emp.setIsActive(rs.getBoolean("ACTIVE"));
                emp.setEducationLevel(rs.getString("EDUCATION_LEVEL_NAME"));
                emp.setIdentifyNumber(rs.getInt("IDENTIFY_NUMBER"));
                emp.setSpecialize(rs.getString("SPECIALIZE_NAME"));
                
                listEmp.add(emp);
            }
            rs.close();
            cn.close();
            }
        } catch (Exception e) {
            
        }
        
        return listEmp;
    }    
    public List<EmployeeEntity> getEmployeeByPosition(int posiId) {
        List<EmployeeEntity> listEmp = new ArrayList<>();
        
        try {
            //1.tao 1 ket noi
            Connection cn = MyLib.getCN();
            if(cn!=null) {
            String sql = "SELECT EMPLOYEE.*, DEPARTMENT.DEPT_NAME, BANK.BANK_NAME, "+
                        "EDUCATION_LEVEL.EDUCATION_LEVEL_NAME, PROVINCE.PROVINCE_NAME, POSITION.*, SPECIALIZE.SPECIALIZE_NAME " +
                        "FROM EMPLOYEE,DEPARTMENT,BANK,EDUCATION_LEVEL,PROVINCE,POSITION,SPECIALIZE " +
                        "WHERE DEPARTMENT.ID = EMPLOYEE.DEPARTMENT_ID AND BANK.ID = EMPLOYEE.BANK_ID AND " +
                        "EDUCATION_LEVEL.ID = EMPLOYEE.EDUCATION_LEVEL_ID AND EMPLOYEE.PROVINCE_ID = PROVINCE.ID " +
                        "AND EMPLOYEE.POSITION_ID = POSITION.ID AND EMPLOYEE.SPECIALIZED_ID = SPECIALIZE.ID " +
                        "AND EMPLOYEE.POSITION_ID = ? "+
                        "AND EMPLOYEE.ACTIVE = ?";
            PreparedStatement pst = cn.prepareStatement(sql);
            pst.setInt(1, posiId);
            pst.setBoolean(2, true);
            //3.cho thi hanh lenh truy van tren doi tuong nay va ket qua se dc tra ve doi tuong resulltset
            ResultSet rs = pst.executeQuery();
            
            //4. duyuet ket qua tra ve trong resultset rs va fill vao arraylist ds.
            while(rs.next()) {
                EmployeeEntity emp = new EmployeeEntity();
                emp.setEmployeeId(rs.getString("ID"));
                emp.setName(rs.getString("NAME"));
                emp.setBirthday(rs.getDate("BIRTHDAY"));
                emp.setGender(rs.getBoolean("GENDER"));
                emp.setEmail(rs.getString("EMAIL"));
                emp.setAddress(rs.getString("ADDRESS"));
                emp.setPhone(rs.getString("PHONE"));
                emp.setAvatar(ApplicationCommon.getAvatarFormDB(rs.getBytes("AVATAR")));
                emp.setEntryDate(rs.getDate("ENTRY_DATE"));
                emp.setBasicSalary(rs.getBigDecimal("BASIC_SALARY"));
                emp.setProvinceName(rs.getString("PROVINCE_NAME"));
                emp.setBankName(rs.getString("BANK_NAME"));
                emp.setAccountNumberBank(rs.getString("ACCOUNT_BANK"));
                emp.setDepartmentName(rs.getString("DEPT_NAME"));
                emp.setPositionName(rs.getString("POSITION_NAME"));
                emp.setBonusSalary(rs.getBigDecimal("BONUS_SALARY"));
                emp.setMaritalStatus(rs.getBoolean("MARITAL_STATUS"));
                emp.setIsActive(rs.getBoolean("ACTIVE"));
                emp.setEducationLevel(rs.getString("EDUCATION_LEVEL_NAME"));
                emp.setIdentifyNumber(rs.getInt("IDENTIFY_NUMBER"));
                emp.setSpecialize(rs.getString("SPECIALIZE_NAME"));
                
                listEmp.add(emp);
            }
            rs.close();
            cn.close();
            }
        } catch (Exception e) {
            
        }
        
        return listEmp;
    }    
    public List<EmployeeEntity> getEmployeeByProvince(int proId) {
        List<EmployeeEntity> listEmp = new ArrayList<>();
        
        try {
            //1.tao 1 ket noi
            Connection cn = MyLib.getCN();
            if(cn!=null) {
            String sql = "SELECT EMPLOYEE.*, DEPARTMENT.DEPT_NAME, BANK.BANK_NAME, "+
                        "EDUCATION_LEVEL.EDUCATION_LEVEL_NAME, PROVINCE.PROVINCE_NAME, POSITION.*, SPECIALIZE.SPECIALIZE_NAME " +
                        "FROM EMPLOYEE,DEPARTMENT,BANK,EDUCATION_LEVEL,PROVINCE,POSITION,SPECIALIZE " +
                        "WHERE DEPARTMENT.ID = EMPLOYEE.DEPARTMENT_ID AND BANK.ID = EMPLOYEE.BANK_ID AND " +
                        "EDUCATION_LEVEL.ID = EMPLOYEE.EDUCATION_LEVEL_ID AND EMPLOYEE.PROVINCE_ID = PROVINCE.ID " +
                        "AND EMPLOYEE.POSITION_ID = POSITION.ID AND EMPLOYEE.SPECIALIZED_ID = SPECIALIZE.ID " +
                        "AND EMPLOYEE.PROVINCE_ID= ? "+
                        "AND EMPLOYEE.ACTIVE = ?";
            PreparedStatement pst = cn.prepareStatement(sql);
            pst.setInt(1, proId);
            pst.setBoolean(2, true);
            //3.cho thi hanh lenh truy van tren doi tuong nay va ket qua se dc tra ve doi tuong resulltset
            ResultSet rs = pst.executeQuery();
            
            //4. duyuet ket qua tra ve trong resultset rs va fill vao arraylist ds.
            while(rs.next()) {
                EmployeeEntity emp = new EmployeeEntity();
                emp.setEmployeeId(rs.getString("ID"));
                emp.setName(rs.getString("NAME"));
                emp.setBirthday(rs.getDate("BIRTHDAY"));
                emp.setGender(rs.getBoolean("GENDER"));
                emp.setEmail(rs.getString("EMAIL"));
                emp.setAddress(rs.getString("ADDRESS"));
                emp.setPhone(rs.getString("PHONE"));
                emp.setAvatar(ApplicationCommon.getAvatarFormDB(rs.getBytes("AVATAR")));
                emp.setEntryDate(rs.getDate("ENTRY_DATE"));
                emp.setBasicSalary(rs.getBigDecimal("BASIC_SALARY"));
                emp.setProvinceName(rs.getString("PROVINCE_NAME"));
                emp.setBankName(rs.getString("BANK_NAME"));
                emp.setAccountNumberBank(rs.getString("ACCOUNT_BANK"));
                emp.setDepartmentName(rs.getString("DEPT_NAME"));
                emp.setPositionName(rs.getString("POSITION_NAME"));
                emp.setBonusSalary(rs.getBigDecimal("BONUS_SALARY"));
                emp.setMaritalStatus(rs.getBoolean("MARITAL_STATUS"));
                emp.setIsActive(rs.getBoolean("ACTIVE"));
                emp.setEducationLevel(rs.getString("EDUCATION_LEVEL_NAME"));
                emp.setIdentifyNumber(rs.getInt("IDENTIFY_NUMBER"));
                emp.setSpecialize(rs.getString("SPECIALIZE_NAME"));
                
                listEmp.add(emp);
            }
            rs.close();
            cn.close();
            }
        } catch (Exception e) {
            
        }
        
        return listEmp;
    }    
    public List<EmployeeEntity> getEmployeeBySpecialize(int specId) {
        List<EmployeeEntity> listEmp = new ArrayList<>();
        
        try {
            //1.tao 1 ket noi
            Connection cn = MyLib.getCN();
            if(cn!=null) {
            String sql = "SELECT EMPLOYEE.*, DEPARTMENT.DEPT_NAME, BANK.BANK_NAME, "+
                        "EDUCATION_LEVEL.EDUCATION_LEVEL_NAME, PROVINCE.PROVINCE_NAME, POSITION.*, SPECIALIZE.SPECIALIZE_NAME " +
                        "FROM EMPLOYEE,DEPARTMENT,BANK,EDUCATION_LEVEL,PROVINCE,POSITION,SPECIALIZE " +
                        "WHERE DEPARTMENT.ID = EMPLOYEE.DEPARTMENT_ID AND BANK.ID = EMPLOYEE.BANK_ID AND " +
                        "EDUCATION_LEVEL.ID = EMPLOYEE.EDUCATION_LEVEL_ID AND EMPLOYEE.PROVINCE_ID = PROVINCE.ID " +
                        "AND EMPLOYEE.POSITION_ID = POSITION.ID AND EMPLOYEE.SPECIALIZED_ID = SPECIALIZE.ID " +
                        "AND EMPLOYEE.SPECIALIZED_ID = ? "+
                        "AND EMPLOYEE.ACTIVE = ?";
            PreparedStatement pst = cn.prepareStatement(sql);
            pst.setInt(1, specId);
            pst.setBoolean(2, true);
            //3.cho thi hanh lenh truy van tren doi tuong nay va ket qua se dc tra ve doi tuong resulltset
            ResultSet rs = pst.executeQuery();
            
            //4. duyuet ket qua tra ve trong resultset rs va fill vao arraylist ds.
            while(rs.next()) {
                EmployeeEntity emp = new EmployeeEntity();
                emp.setEmployeeId(rs.getString("ID"));
                emp.setName(rs.getString("NAME"));
                emp.setBirthday(rs.getDate("BIRTHDAY"));
                emp.setGender(rs.getBoolean("GENDER"));
                emp.setEmail(rs.getString("EMAIL"));
                emp.setAddress(rs.getString("ADDRESS"));
                emp.setPhone(rs.getString("PHONE"));
                emp.setAvatar(ApplicationCommon.getAvatarFormDB(rs.getBytes("AVATAR")));
                emp.setEntryDate(rs.getDate("ENTRY_DATE"));
                emp.setBasicSalary(rs.getBigDecimal("BASIC_SALARY"));
                emp.setProvinceName(rs.getString("PROVINCE_NAME"));
                emp.setBankName(rs.getString("BANK_NAME"));
                emp.setAccountNumberBank(rs.getString("ACCOUNT_BANK"));
                emp.setDepartmentName(rs.getString("DEPT_NAME"));
                emp.setPositionName(rs.getString("POSITION_NAME"));
                emp.setBonusSalary(rs.getBigDecimal("BONUS_SALARY"));
                emp.setMaritalStatus(rs.getBoolean("MARITAL_STATUS"));
                emp.setIsActive(rs.getBoolean("ACTIVE"));
                emp.setEducationLevel(rs.getString("EDUCATION_LEVEL_NAME"));
                emp.setIdentifyNumber(rs.getInt("IDENTIFY_NUMBER"));
                emp.setSpecialize(rs.getString("SPECIALIZE_NAME"));
                
                listEmp.add(emp);
            }
            rs.close();
            cn.close();
            }
        } catch (Exception e) {
            
        }
        
        return listEmp;
    }    
    public List<EmployeeEntity> getEmployeeByEducation(int eduId) {
        List<EmployeeEntity> listEmp = new ArrayList<>();
        
        try {
            //1.tao 1 ket noi
            Connection cn = MyLib.getCN();
            if(cn!=null) {
            String sql = "SELECT EMPLOYEE.*, DEPARTMENT.DEPT_NAME, BANK.BANK_NAME, "+
                        "EDUCATION_LEVEL.EDUCATION_LEVEL_NAME, PROVINCE.PROVINCE_NAME, POSITION.*, SPECIALIZE.SPECIALIZE_NAME " +
                        "FROM EMPLOYEE,DEPARTMENT,BANK,EDUCATION_LEVEL,PROVINCE,POSITION,SPECIALIZE " +
                        "WHERE DEPARTMENT.ID = EMPLOYEE.DEPARTMENT_ID AND BANK.ID = EMPLOYEE.BANK_ID AND " +
                        "EDUCATION_LEVEL.ID = EMPLOYEE.EDUCATION_LEVEL_ID AND EMPLOYEE.PROVINCE_ID = PROVINCE.ID " +
                        "AND EMPLOYEE.POSITION_ID = POSITION.ID AND EMPLOYEE.SPECIALIZED_ID = SPECIALIZE.ID " +
                        "AND EMPLOYEE.EDUCATION_LEVEL_ID = ? "+
                        "AND EMPLOYEE.ACTIVE = ?";
            PreparedStatement pst = cn.prepareStatement(sql);
            pst.setInt(1, eduId);
            pst.setBoolean(2, true);
            //3.cho thi hanh lenh truy van tren doi tuong nay va ket qua se dc tra ve doi tuong resulltset
            ResultSet rs = pst.executeQuery();
            
            //4. duyuet ket qua tra ve trong resultset rs va fill vao arraylist ds.
            while(rs.next()) {
                EmployeeEntity emp = new EmployeeEntity();
                emp.setEmployeeId(rs.getString("ID"));
                emp.setName(rs.getString("NAME"));
                emp.setBirthday(rs.getDate("BIRTHDAY"));
                emp.setGender(rs.getBoolean("GENDER"));
                emp.setEmail(rs.getString("EMAIL"));
                emp.setAddress(rs.getString("ADDRESS"));
                emp.setPhone(rs.getString("PHONE"));
                emp.setAvatar(ApplicationCommon.getAvatarFormDB(rs.getBytes("AVATAR")));
                emp.setEntryDate(rs.getDate("ENTRY_DATE"));
                emp.setBasicSalary(rs.getBigDecimal("BASIC_SALARY"));
                emp.setProvinceName(rs.getString("PROVINCE_NAME"));
                emp.setBankName(rs.getString("BANK_NAME"));
                emp.setAccountNumberBank(rs.getString("ACCOUNT_BANK"));
                emp.setDepartmentName(rs.getString("DEPT_NAME"));
                emp.setPositionName(rs.getString("POSITION_NAME"));
                emp.setBonusSalary(rs.getBigDecimal("BONUS_SALARY"));
                emp.setMaritalStatus(rs.getBoolean("MARITAL_STATUS"));
                emp.setIsActive(rs.getBoolean("ACTIVE"));
                emp.setEducationLevel(rs.getString("EDUCATION_LEVEL_NAME"));
                emp.setIdentifyNumber(rs.getInt("IDENTIFY_NUMBER"));
                emp.setSpecialize(rs.getString("SPECIALIZE_NAME"));
                
                listEmp.add(emp);
            }
            rs.close();
            cn.close();
            }
        } catch (Exception e) {
            
        }
        
        return listEmp;
    }    
     
    public EmployeeEntity getEmployeeById(String employeeId) {
        //tra ve 1 danh sach cac doi tuong nguoi dung trong bang Tbuser cua database
      EmployeeEntity employee = new EmployeeEntity();
        try {
            //1.tao 1 ket noi
            Connection cn = MyLib.getCN();
            if(cn!=null) {
            String sql = "SELECT EMPLOYEE.*, DEPARTMENT.DEPT_NAME, BANK.BANK_NAME, "+
                        "EDUCATION_LEVEL.EDUCATION_LEVEL_NAME, PROVINCE.PROVINCE_NAME, POSITION.*, SPECIALIZE.SPECIALIZE_NAME " +
                        "FROM EMPLOYEE,DEPARTMENT,BANK,EDUCATION_LEVEL,PROVINCE,POSITION,SPECIALIZE " +
                        "WHERE DEPARTMENT.ID = EMPLOYEE.DEPARTMENT_ID AND BANK.ID = EMPLOYEE.BANK_ID AND " +
                        "EDUCATION_LEVEL.ID = EMPLOYEE.EDUCATION_LEVEL_ID AND EMPLOYEE.PROVINCE_ID = PROVINCE.ID " +
                        "AND EMPLOYEE.POSITION_ID = POSITION.ID AND EMPLOYEE.SPECIALIZED_ID = SPECIALIZE.ID " +
                        "AND EMPLOYEE.ID = ?";
            PreparedStatement pst = cn.prepareStatement(sql);
            pst.setString(1, employeeId);
            //3.cho thi hanh lenh truy van tren doi tuong nay va ket qua se dc tra ve doi tuong resulltset
            ResultSet rs = pst.executeQuery();
                                  
            //4. duyuet ket qua tra ve trong resultset rs va fill vao arraylist ds.
            while(rs.next()) {
                employee.setEmployeeId(rs.getString("ID"));
                employee.setName(rs.getString("NAME"));
                employee.setBirthday(rs.getDate("BIRTHDAY"));
                employee.setGender(rs.getBoolean("GENDER"));
                employee.setEmail(rs.getString("EMAIL"));
                employee.setAddress(rs.getString("ADDRESS"));
                employee.setPhone(rs.getString("PHONE"));
                employee.setAvatar(ApplicationCommon.getAvatarFormDB(rs.getBytes("AVATAR")));
                employee.setEntryDate(rs.getDate("ENTRY_DATE"));
                employee.setBasicSalary(rs.getBigDecimal("BASIC_SALARY"));
                employee.setProvinceName(rs.getString("PROVINCE_NAME"));
                employee.setBankName(rs.getString("BANK_NAME"));
                employee.setAccountNumberBank(rs.getString("ACCOUNT_BANK"));
                employee.setDepartmentName(rs.getString("DEPT_NAME"));
                employee.setPositionName(rs.getString("POSITION_NAME"));
                employee.setBonusSalary(rs.getBigDecimal("BONUS_SALARY"));
                employee.setMaritalStatus(rs.getBoolean("MARITAL_STATUS"));
                employee.setIsActive(rs.getBoolean("ACTIVE"));
                employee.setEducationLevel(rs.getString("EDUCATION_LEVEL_NAME"));
                employee.setIdentifyNumber(rs.getInt("IDENTIFY_NUMBER"));
                employee.setSpecialize(rs.getString("SPECIALIZE_NAME"));
                
                
            }
            rs.close();
            cn.close();
            }
        } catch (Exception e) {
            
        }
        
        return employee;
    }
    
    //them du lieu vao database,chen 1 doi tuong lop hoc vao ben trong co so du lieu
     
    public boolean insert(EmployeeEntity emp) {
        
        try {
            //1. tao ket noi
            Connection cn = MyLib.getCN();
            
            //2.tao doi tuong lenh insert sql
            String sql = "insert EMPLOYEE values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement pst = cn.prepareStatement(sql);
            pst.setString(1, emp.getEmployeeId());
            pst.setString(2, emp.getName());
            pst.setDate(3, new java.sql.Date(emp.getBirthday().getTime()));
            pst.setBoolean(4, emp.isGender());
            pst.setString(5, emp.getEmail());
            pst.setString(6, emp.getAddress());
            pst.setString(7, emp.getPhone());
            pst.setBinaryStream(8, new FileInputStream(emp.getAvatar()));
            pst.setDate(9, new java.sql.Date(emp.getEntryDate().getTime()));
            pst.setBigDecimal(10, emp.getBasicSalary());
            pst.setInt(11, emp.getProvinceId());
            pst.setInt(12, emp.getBankId());
            pst.setInt(13, emp.getDepartmentId());
            pst.setInt(14, emp.getPositionId());
            pst.setBoolean(15, emp.isMaritalStatus());
            pst.setBoolean(16, true);
            pst.setInt(17, emp.getEducationLevelId());
            pst.setInt(18, emp.getIdentifyNumber());
            pst.setInt(19, emp.getSpecializeId());
            pst.setString(20, emp.getAccountNumberBank());
            
            
            //3.cho thi hanh cau lenh sql
            int r = pst.executeUpdate();
            pst.close();
            cn.close();
            return (r>0);
            
            
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
      
    }
    public boolean update(EmployeeEntity emp) {
        
        try {
            //1. tao ket noi
            Connection cn = MyLib.getCN();
            
            //2.tao doi tuong lenh insert sql
            String sql = "UPDATE EMPLOYEE SET NAME = ?, BIRTHDAY = ?, GENDER = ?, EMAIL = ?, ADDRESS = ?, PHONE = ?, "+
                    "AVATAR = ?, ENTRY_DATE = ?, BASIC_SALARY = ?, PROVINCE_ID = ?, BANK_ID = ?, DEPARTMENT_ID = ?, POSITION_ID = ?, "+
                    "MARITAL_STATUS = ?, ACTIVE = ?, EDUCATION_LEVEL_ID = ?, IDENTIFY_NUMBER = ?, SPECIALIZED_ID = ?, ACCOUNT_BANK = ? "+
                    "WHERE EMPLOYEE.ID = ?";
            PreparedStatement pst = cn.prepareStatement(sql);
            pst.setString(1, emp.getName());
            pst.setDate(2, new java.sql.Date(emp.getBirthday().getTime()));
            pst.setBoolean(3, emp.isGender());
            pst.setString(4, emp.getEmail());
            pst.setString(5, emp.getAddress());
            pst.setString(6, emp.getPhone());
            pst.setBinaryStream(7, new FileInputStream(emp.getAvatar()));
            pst.setDate(8, new java.sql.Date(emp.getEntryDate().getTime()));
            pst.setBigDecimal(9, emp.getBasicSalary());
            pst.setInt(10, emp.getProvinceId());
            pst.setInt(11, emp.getBankId());
            pst.setInt(12, emp.getDepartmentId());
            pst.setInt(13, emp.getPositionId());
            pst.setBoolean(14, emp.isMaritalStatus());
            pst.setBoolean(15, emp.isIsActive());
            pst.setInt(16, emp.getEducationLevelId());
            pst.setInt(17, emp.getIdentifyNumber());
            pst.setInt(18, emp.getSpecializeId());
            pst.setString(19, emp.getAccountNumberBank());
            pst.setString(20, emp.getEmployeeId());
            //3.cho thi hanh cau lenh sql
            int r = pst.executeUpdate();
            pst.close();
            cn.close();
            return (r > 0);
            
            
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
      
    }
    
    public boolean changeStatusEmployee(boolean active, String Id) {
        
        try {
            //1. tao ket noi
            Connection cn = MyLib.getCN();
            
            //2.tao doi tuong lenh insert sql
            String sql = "UPDATE EMPLOYEE SET ACTIVE = ? "+
                    "WHERE EMPLOYEE.ID = ?";
            PreparedStatement pst = cn.prepareStatement(sql);
            pst.setBoolean(1, active);
            pst.setString(2, Id);
            
            //3.cho thi hanh cau lenh sql
            int r = pst.executeUpdate();
            pst.close();
            cn.close();
            return (r > 0);
            
            
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
      
    }
    
    public boolean delete(String employeeId) {
        
        try {
            //1. tao ket noi
            Connection cn = MyLib.getCN();
            
            //2.tao doi tuong lenh insert sql
            String sql = "delete from EMPLOYEE where ID = ?";
            PreparedStatement pst = cn.prepareStatement(sql);
           
            pst.setString(1, employeeId);
            
            //3.cho thi hanh cau lenh sql
            int r = pst.executeUpdate();
            pst.close();
            cn.close();
            return (r > 0);
            
            
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
      
    }
    public String topId(){
        String s = null;
        try {
            // 1. tao 1 ket noi
            Connection cn = MyLib.getCN();
            if(cn!= null){
                Statement st = cn.createStatement();
                
                
                ResultSet rs = st.executeQuery("select Top 1 ID from EMPLOYEE order by ID desc");
                while(rs.next())
                {
                s = rs.getString(1);
                }
                rs.close();
                cn.close();
            }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "select id fail");
        }
        
        return s;
    }
//    //tim kiem lop hoc co ma so id
//    //tim thanh cong doi tuong lo phoc se dc tra ve tuong ung
//    //nguoc lai gia tri tra ve se la null
//    public TbSubjects search(String id) {
//        TbSubjects b = null;
//        
//        try {
//            //1. tao ket noi
//            Connection cn = MyLib.getCN();
//            
//            //2.tao doi tuong lenh insert sql
//            String sql = "select * from Subjects where Sub_ID like ?";
//            PreparedStatement pst = cn.prepareStatement(sql);
//            pst.setString(1, id);
//            
//            
//            //3.cho thi hanh cau lenh sql
//            ResultSet rs = pst.executeQuery();
//            
//            //4. Kiem tra co tim thay lop hoc voi ma so nay khong
//            
//            if(rs.next()) {
//                b = new TbSubjects();
//                b.sub_id = rs.getString(1);
//                b.sub_name = rs.getString(2);
//                b.credit = rs.getInt(3);
//                b.theo_hour = rs.getInt(4);
//                b.prac_hour = rs.getInt(5);
//                b.fees = rs.getInt(6);
//                b.semester = rs.getInt(7);
//            }
//            
//            cn.close();
//            
//            
//            
//        } catch (Exception e) {
//            e.printStackTrace();
//            
//        }
//        
//        return b;
//    } 
}
